﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CRMFramework;
using System.Collections;
using Newtonsoft.Json;
using CRMEntity;

namespace CRMBiz
{
    public static class ApplicationDetailHandler
    {

        public static DataTable BindDataTableForApplication(int appid)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", appid),
                };
                string sql = "SELECT crm_applicationcontact.app_id, crm_contact.FirstName, crm_contact.MiddleName, crm_contact.SurName, crm_contact.Mobile, crm_contact.HomePhone, crm_contact.Email, (SELECT  app_LoanValue FROM  crm_loans WHERE (app_id = crm_applicationcontact.app_id)LIMIT 1) AS loanvalue,(SELECT app_LoanAmount FROM  crm_loaninfo WHERE (app_id = crm_applicationcontact.app_id)) AS LoanAmount, crm_applicationextend.LoadNo, crm_applicationextend.Folder,  crm_applicationextend.AwaitDocs, crm_lastresult.ResultDescription, (select count(*) from crm_vehicles where crm_vehicles.app_id=crm_applicationextend.app_id) as Motor,(select count(*) from crm_realestate where crm_realestate.app_id=crm_applicationextend.app_id) as Property FROM  crm_lastresult LEFT OUTER JOIN crm_applicationextend ON crm_lastresult.ResultID = crm_applicationextend.ResultID RIGHT OUTER JOIN crm_applicationcontact LEFT OUTER JOIN crm_contact ON crm_applicationcontact.ContactID = crm_contact.ContactID ON crm_applicationextend.app_id = crm_applicationcontact.app_id WHERE (crm_applicationcontact.NoInApplication = 1) AND (crm_applicationcontact.app_id = @app_id)";
                dt = DBhelper.ExecuteDataset(sql, para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataTable GetDistinctComesFrom()
        {
            DataTable dt = null;
            try
            {
                dt = DBhelper.ExecuteDataset("Select 'ALL' as comesfrom  UNION Select Distinct comesfrom From crm_application Where (comesfrom IS NOT NUll OR comesfrom <> '')").Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        public static bool UpdateAppDetailData(string appid, string loanAmount, string loanValue, string firstName, string middleName, string surName, string mobile, string homePhone, string email, string resultDescription, string loadNo, string awaitDocs, string folder)
        {
            bool flag = false;
            try
            {
                var para = new[]{
                DBhelper.SetParameterForApp("@app_LoanAmount",loanAmount),
                DBhelper.SetParameterForApp("@app_LoanValue",loanValue),
                DBhelper.SetParameterForApp("@FirstName",firstName),
                DBhelper.SetParameterForApp("@MiddleName",middleName),
                DBhelper.SetParameterForApp("@SurName",surName),
                DBhelper.SetParameterForApp("@Mobile",mobile),
                DBhelper.SetParameterForApp("@HomePhone",homePhone),
                DBhelper.SetParameterForApp("@Email",email),
                DBhelper.SetParameterForApp("@ResultDescription",resultDescription),
                DBhelper.SetParameterForApp("@LoadNo",loadNo),
                DBhelper.SetParameterForApp("@AwaitDocs",awaitDocs),
                DBhelper.SetParameterForApp("@Folder",folder),
                DBhelper.SetParameterForApp("@app_id", appid),
                };

                string sqlQuery = "update crm_applicationextend set crm_applicationextend.LoadNo=@LoadNo,crm_applicationextend.AwaitDocs=@AwaitDocs,crm_applicationextend.Folder=@Folder where crm_applicationextend.app_id=@app_id; update crm_loans,crm_loaninfo,crm_contact,crm_lastresult SET crm_loans.app_LoanValue=@app_LoanValue,crm_loaninfo.app_LoanAmount=@app_LoanAmount,crm_contact.FirstName=@FirstName,crm_contact.MiddleName=@MiddleName,crm_contact.SurName=@SurName,crm_contact.Mobile=@Mobile,crm_contact.HomePhone=@HomePhone,crm_contact.Email=@Email,crm_lastresult.ResultDescription=@ResultDescription WHERE crm_loans.app_id=@app_id and crm_loaninfo.app_id=@app_id and crm_contact.ContactID=(select ContactID from crm_applicationcontact where crm_applicationcontact.app_id=@app_id) and ResultID=(select ResultID from crm_applicationextend where app_id=@app_id)";
                DBhelper.ExecuteNonQuery(sqlQuery, para);
                flag = true;
            }
            catch (Exception)
            {
                throw;
            }
            return flag;
        }

        public static string selectApplicationType(int appid)
        {
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@appid", appid)
                };
                return DBhelper.ExecuteScalar("Select PurposeSummary from crm_application where app_id=@appid", para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataSet SelectApplicationDetail(int app_id)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                };

                StringBuilder strQuery = new StringBuilder("Select app_id, udf_getformatedDate(CreatedDate) as CreatedDate,DesiredAmount ,(ifnull(amountMoto,0)+ifnull(amoutProperties,0)) as Security  FROM crm_application where crm_application.app_id=@app_id");
                ds = DBhelper.ExecuteDataset(strQuery.ToString(), para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        //Delete application for given app_id
        public static string DeleteSingleApplication(int app_id)
        {
            DataTable dt = new DataTable();
            try
            {
                string strQuery = string.Empty;
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                };

                strQuery = "SELECT IFNULL(SUM(Income_Payslip + Bank_LatestMonth+Bank_FullName+Bank_ACBSB+Assert_CouncilRate+Assert_Vehicles+Assert_CouncilRate+Assert_Mortage+Assert_Valuation+IDs_Residential_Rental+Residential_Rate+IDs_Residential_Boarding+IDs_UtilityBill+IDs_PhotoID+IDs_Card+ExtraItemNo1+ExtraItemNo2+ExtraItemNo3+ExtraItemNo4+ExtraItemNo5+ExtraItemNo6+ExtraItemNo7+ExtraItemNo8+ExtraItemNo9+ExtraItemNo10 ),0) AS CheckCount  FROM  crm_appcontactchecklist WHERE app_id=@app_id";

                //StringBuilder sql = new StringBuilder("SELECT (SELECT COUNT( * ) FROM am_loan1 WHERE app_id=@app_id) AS LoanCount,")
                //   .Append(" (SELECT IFNULL(SUM(Income_Payslip + Bank_LatestMonth+Bank_FullName+Bank_ACBSB+Assert_CouncilRate+Assert_Vehicles+Assert_CouncilRate+Assert_Mortage+Assert_Valuation+IDs_Residential_Rental+Residential_Rate+IDs_Residential_Boarding+IDs_UtilityBill+IDs_PhotoID+IDs_Card+ExtraItemNo1+ExtraItemNo2+ExtraItemNo3+ExtraItemNo4+ExtraItemNo5+ExtraItemNo6+ExtraItemNo7+ExtraItemNo8+ExtraItemNo9+ExtraItemNo10 ),0) AS CheckCount ")
                //   .Append(" FROM  crm_appcontactchecklist WHERE app_id = @app_id) AS CheckCount");

                dt = DBhelper.ExecuteDataset(strQuery, para).Tables[0];
                string strloanstatus = DBhelper.ExecuteScalar("Select status from crm_application where app_id=@app_id", para);

                if (strloanstatus.Equals("Active"))
                    return "active";

                if ((Convert.ToInt32(dt.Rows[0]["CheckCount"]) == 0))
                {
                    string SqlQuery = "delete FROM crm_applicationappform WHERE ApplicationId=@app_id and AppFormId=@app_id;update crm_application set deletedFlag=1 where app_id=@app_id";
                    DBhelper.ExecuteNonQuery(SqlQuery, para);
                    return "deleted";
                }
                else if (Convert.ToInt32(dt.Rows[0]["CheckCount"]) != 0)
                    return "Check list is exist you can not delete this application.";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ("success");
        }

        public static DataTable BindApplicationForSMSFields(int appid)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", appid),
                };
                string sqlqry = "select a.*, c.* from crm_application a inner join crm_applicationcontact ac on ac.app_id=a.app_id inner join crm_contact c on c.ContactID=ac.ContactID where a.app_id=@app_id";
                dt = DBhelper.ExecuteDataset(sqlqry, para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static bool DeleteMultipleApplication(string applications)
        {
            bool flag = false;
            try
            {
                if (!string.IsNullOrEmpty(applications))
                {
                    var para = new[] {
                        DBhelper.SetParameter("@applications", applications),
                    };
                    DBhelper.ExecuteNonQuery("Update crm_application SET deletedFlag=1 Where app_id IN (@applications)", para);
                    flag = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
        /// <summary>
        /// Get contact id list by name -- from Wade 20140213
        /// </summary>
        /// <param name="app_id"></param>
        /// <param name="firstname"></param>
        /// <param name="middlename"></param>
        /// <param name="surname"></param>
        /// <returns></returns>
        public static DataTable GetContactIdByName(int app_id, string firstname, string middlename, string surname)
        {
            string query = " SELECT ContactId from crm_contact Where deletedFlag=0";
            var sbSql = new StringBuilder(query);
            var searchByNameCondition = new StringBuilder(" and ").AppendFormat("FirstName= '{0}'", firstname)
                                                                  .AppendFormat(" and SurName= '{0}'", surname);

            if (!string.IsNullOrEmpty(middlename))
            {
                searchByNameCondition.AppendFormat(" and MiddleName= '{0}'", middlename);
            }

            sbSql.Append(searchByNameCondition);
            DataTable tb = DBhelper.ExecuteDataset(sbSql.ToString()).Tables[0];
            int tempContactId = 0;

            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    if (int.TryParse(tb.Rows[i]["ContactId"].ToString(), out tempContactId))
                    {
                        if (isValidContact(app_id, tempContactId) != 1)
                        {
                            tb.Rows.Remove(tb.Rows[i]);
                        }
                    }
                }
            }
            return tb;
        }

        public static int isValidContact(int app_id, int contactID)
        {
            int retunVal = 1;
            int countCRM_Contact = 0;

            DataTable dt = null;
            try
            {

                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                    DBhelper.SetParameter("@p_Contact_ID",contactID)
                };
                countCRM_Contact = int.Parse(DBhelper.ExecuteScalar("Select count(*) From crm_contact Where ContactID=@p_Contact_ID AND deletedFlag=0  AND (DupwithContact IS NULL OR DupwithContact='')", para));



                if (countCRM_Contact == 0)
                    retunVal = 2;
                else
                {
                    int combinedAmount = int.Parse(DBhelper.ExecuteScalar("Select count(*) From  crm_applicationcontact Where ContactID=@p_Contact_ID AND deletedFlag=0 AND combineapp >0", para));
                    if (combinedAmount > 0)
                    {
                        retunVal = 3;
                    }
                    else
                    {
                        dt = DBhelper.ExecuteDataset("SELECT ContactID FROM  crm_applicationcontact WHERE app_id=@app_id AND deletedFlag=0 ", para).Tables[0];
                        foreach (DataRow dr in dt.Rows)
                        {
                            if (int.Parse(dr["ContactID"].ToString()) == contactID)
                                retunVal = 0;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return retunVal;
        }

        public static bool combineApplication(int currentAppID, int ContactID, int combineAppID, string hOperator)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_current_appid",currentAppID),
                    DBhelper.SetParameter("@p_ContactID",ContactID),
                    DBhelper.SetParameter("@p_combine_appid",combineAppID),
                    DBhelper.SetParameter("@p_hOperator",hOperator),
                };
                DBhelper.ExecuteNonQuerySP("sp_CombineApplication", para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
        public static bool disCombineApplication(int currentAppID, int ContactID, int combineAppID, string hOperator)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_current_appid",currentAppID),
                    DBhelper.SetParameter("@p_ContactID",ContactID),                
                    DBhelper.SetParameter("@p_hOperator",hOperator),
                };
                DBhelper.ExecuteNonQuerySP("sp_disCombineApplication", para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}

